package com.gxa.service.employee.impl;

import com.gxa.common.R.ResponseData;
import com.gxa.mapper.employee.ExcelMapper;
import com.gxa.pojo.Employee;
import com.gxa.service.employee.ExcelService;
import com.gxa.utils.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *excel文件上传批量导入员工信息
 */
@Service
public class ExcelServiceImpl implements ExcelService {
    //String filePath, String deptId
    @Autowired(required = false)
    private ExcelMapper excelMapper;
    @Override
    public ResponseData excelRead(MultipartFile file, String deptId) throws IOException, ParseException {
        int deptId_int = 0;
        if (deptId!=null && deptId !=""){
             deptId_int = Integer.parseInt(deptId);
        }
        //初始化对象
        List<Employee> excelListData = new ArrayList<>();
        //读取文件的输入流
//        File file = new File("C:\\Users\\L\\Desktop\\人事管理系统\\employeeDate.xlsx");
        //创建一个输入流：读
//        FileInputStream fileInputStream = new FileInputStream(file);
        //获取Wookbook对象
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
//        Workbook workbook =  new HSSFWorkbook(fileInputStream);
        System.out.println(workbook);
        //获取sheet对象
        Sheet sheet = workbook.getSheetAt(0);
        //获取总的行数
        int rows = sheet.getLastRowNum();
        System.out.println(rows);
        //遍历行
        for (int r = 1; r <= rows; r++){
            //每次循环一个对象
            List<String> rowsData = new ArrayList<>();
            //先获取对应行的对象
            Row row = sheet.getRow(r);
            //获取总的列数
            long columns = row.getLastCellNum();
            //遍历列
            for (int c = 0; c < columns; c++) {
                //获取对应格子里的数据
                Cell cell = row.getCell(c);
                //获取对应格子里存放的数据类型
                CellType cellType = cell.getCellType();
                //每个格子的初始值
                String value = "";
                //类型转换
                switch (cellType){
                    case STRING: //字符串型
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC: //数值型
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                            value = sdf.format(date);
                        } else {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        }
                        break;
                    case FORMULA: //公式型
//                        value = Double.valueOf(cell.getNumericCellValue()).toString();
                        value = cell.getCellFormula();
                        break;
                    case BLANK: //空值
                        value = "";
                        break;
                    case BOOLEAN: //布尔型
                        value = Boolean.valueOf(cell.getBooleanCellValue()).toString();
                        break;
                    case ERROR: //错误
                        value = "error";
                        break;
                    default: //其他
                        value = "未知";
                        break;
                }
                rowsData.add(value);
            }
            //将对象塞到list中
             int j = 0;
            Employee employee = new Employee();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            while (j < rowsData.size()){
                employee.setEname(rowsData.get(j++));
                employee.setSex(Integer.parseInt(rowsData.get(j++)));
                if (rowsData.get(j) != null && !rowsData.get(j).equals("")){
                    employee.setBirthday(new java.sql.Date(sdf.parse(rowsData.get(j++)).getTime()));
                }else {
                    j++;
                }
                employee.setPhone(rowsData.get(j++));
                employee.setEducated(rowsData.get(j++));
                employee.setMaritalStatus(rowsData.get(j++));
                employee.setCurrentAddress(rowsData.get(j++));
                employee.setBloodType(rowsData.get(j++));
                employee.setHealthStatus(rowsData.get(j++));
                if (rowsData.get(j) != null && !rowsData.get(j).equals("")){
                    employee.setEntryDate(new java.sql.Date(sdf.parse(rowsData.get(j++)).getTime()));
                }else {
                    j++;
                }

                if (deptId_int!=0){
                    employee.setDeptId(deptId_int);
                    j++;
                }else {
                    employee.setDeptId(Integer.parseInt(rowsData.get(j++)));
                }
                employee.setZhiCheng(rowsData.get(j++));
                employee.setZhiWu(rowsData.get(j++));
                employee.setPeopleType(rowsData.get(j++));
                employee.setMinZu(rowsData.get(j++));
                employee.setJiGuan(rowsData.get(j++));
                employee.setIdentityCardNumber(rowsData.get(j++));
                employee.setFamilyAddress(rowsData.get(j++));
                employee.setHeight(rowsData.get(j++));
                employee.setWeight(rowsData.get(j++));

                employee.setPoliticalFace(rowsData.get(j++));
                employee.setQq(rowsData.get(j++));
                employee.setTelephone(rowsData.get(j++));
                employee.setEmail(rowsData.get(j++));
                employee.setJiNeng(rowsData.get(j++));
                employee.setWorkExperience(rowsData.get(j++));
                employee.setWinningExperience(rowsData.get(j++));
                employee.setYearAssess(rowsData.get(j++));
                employee.setPeopleNote(rowsData.get(j++));
                employee.setPeoplePhoto(rowsData.get(j++));

                employee.setEntryFormPhoto(rowsData.get(j++));
                employee.setIdentityCardPhoto(rowsData.get(j++));
                employee.setStates(Integer.parseInt(rowsData.get(j++)));
                if (rowsData.get(j) != null && !rowsData.get(j).equals("")){
                    employee.setQuitDate(new java.sql.Date(sdf.parse(rowsData.get(j++)).getTime()));
                }else {
                    j++;
                }
                employee.setQuitReasons(rowsData.get(j++));
                employee.setQuitFormPhoto(rowsData.get(j++));
                if (rowsData.get(j) != null && !rowsData.get(j).equals("")){
                    employee.setBlackDate(new java.sql.Date(sdf.parse(rowsData.get(j++)).getTime()));
                }else {
                    j++;
                }
                if (rowsData.get(j) != null && !rowsData.get(j).equals("")){
                    employee.setJieFengDate(new java.sql.Date(sdf.parse(rowsData.get(j++)).getTime()));
                }else {
                    j++;
                }
                employee.setBlackReasons(rowsData.get(j++));
            }
            excelListData.add(employee);
        }
        System.out.println(excelListData);
            excelMapper.addEmployeeList(excelListData);
        ResponseData responseData = new ResponseData();
        responseData.setCode(200);
        responseData.setMsg("导入员工信息成功");
        return responseData;
    }

    @Override
    public ResponseData excelWrite(HttpServletResponse response,String deptId) throws IOException {
        //查询员工信息表中所有数据
        List<Employee> employeeList = excelMapper.selectAllEmployeeInfo(deptId);
//        System.out.println(employeeList);
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("员工表");
        //处理表头
        String[] titles = new String[]{"员工ID","姓名","性别","出生日期","员工电话","学历","婚姻状态","现住址","血型","健康转态",
                "入职日期","部门编号","职称","职务","人员类别","民族","籍贯","身份证号","家庭住址","身高",
                "体重","政治面貌","员工QQ","员工手机","员工邮箱","掌握技能","工作经验","获奖经历","年度考核情况","员工备注",
                "员工照片","入职表照片","身份证照片","状态","离职日期","离职原因","离职表照片","加黑日期","解封日期","加黑愿意"};
        //创建标题行
        Row titleRow = sheet.createRow(0);
        Cell cell = null;
        for (int i = 0; i < titles.length; i++) {
            Cell titleRowCell = titleRow.createCell(i);
            titleRowCell.setCellValue(titles[i]);
        }
        //处理内容
        int rowNum = 1;
        Row row = null;
        for (Employee employee:employeeList) {

            row = sheet.createRow(rowNum);

            row.createCell(0).setCellValue(employee.getId());
            row.createCell(1).setCellValue(employee.getEname());
            row.createCell(2).setCellValue(employee.getSex());
            row.createCell(3).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(employee.getBirthday()));
            row.createCell(4).setCellValue(employee.getPhone());
            row.createCell(5).setCellValue(employee.getEducated());
            row.createCell(6).setCellValue(employee.getMaritalStatus());
            row.createCell(7).setCellValue(employee.getCurrentAddress());
            row.createCell(8).setCellValue(employee.getBloodType());
            row.createCell(9).setCellValue(employee.getHealthStatus());

            row.createCell(10).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(employee.getEntryDate()));
            row.createCell(11).setCellValue(employee.getDeptId());
            row.createCell(12).setCellValue(employee.getZhiCheng());
            row.createCell(13).setCellValue(employee.getZhiWu());
            row.createCell(14).setCellValue(employee.getPeopleType());
            row.createCell(15).setCellValue(employee.getMinZu());
            row.createCell(16).setCellValue(employee.getJiGuan());
            row.createCell(17).setCellValue(employee.getIdentityCardNumber());
            row.createCell(18).setCellValue(employee.getFamilyAddress());
            row.createCell(19).setCellValue(employee.getHeight());

            row.createCell(20).setCellValue(employee.getWeight());
            row.createCell(21).setCellValue(employee.getPoliticalFace());
            row.createCell(22).setCellValue(employee.getQq());
            row.createCell(23).setCellValue(employee.getTelephone());
            row.createCell(24).setCellValue(employee.getEmail());
            row.createCell(25).setCellValue(employee.getJiNeng());
            row.createCell(26).setCellValue(employee.getWorkExperience());
            row.createCell(27).setCellValue(employee.getWinningExperience());
            row.createCell(28).setCellValue(employee.getYearAssess());
            row.createCell(29).setCellValue(employee.getPeopleNote());

            row.createCell(30).setCellValue(employee.getPeoplePhoto());
            row.createCell(31).setCellValue(employee.getEntryFormPhoto());
            row.createCell(32).setCellValue(employee.getIdentityCardPhoto());
            row.createCell(33).setCellValue(employee.getStates());
            if (employee.getQuitDate()!=null &&  !employee.getQuitDate().equals("")){
                row.createCell(34).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(employee.getQuitDate()));
            }else {
                row.createCell(34).setCellValue("");
            }
            row.createCell(35).setCellValue(employee.getQuitReasons());
            row.createCell(36).setCellValue(employee.getQuitFormPhoto());
//            row.createCell(37).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(employee.getBlackDate()));
//            row.createCell(38).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(employee.getJieFengDate()));
            if (employee.getBlackDate()!=null &&  !employee.getBlackDate().equals("")){
                row.createCell(37).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(employee.getBlackDate()));
            }else {
                row.createCell(37).setCellValue("");
            }
            if (employee.getJieFengDate()!=null &&  !employee.getJieFengDate().equals("")){
                row.createCell(38).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(employee.getJieFengDate()));
            }else {
                row.createCell(38).setCellValue("");
            }
//            row.createCell(37).setCellValue(employee.getBlackDate());
//            row.createCell(38).setCellValue(employee.getJieFengDate());
            row.createCell(39).setCellValue(employee.getBlackReasons());

            rowNum++;
        }
        String fileName = "employee";
        String newFileName = URLEncoder.encode(fileName,"ISO8859-1");
        ServletOutputStream outputStream = response.getOutputStream();
        response.addHeader("Content-Disposition", "attachment;filename=" + newFileName+".xlsx");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//        response.setContentType("application/vnd.ms-excel");
        workbook.write(outputStream);
        ResponseData responseData = new ResponseData();
        responseData.setCode(200);
        responseData.setMsg("导出员工信息成功");
        return responseData;
    }
}
